Python App Shop with Flask Web Development and Jinja

PIC16B: Homework 3

homework
Author

Kenny Guo

Published

February 16, 2025

你好朋友! In this assignment, our goal was to program a Python Flask app to host a dyanmic webpage for a small shop. We would be pulling from a products.db filled with a bunch of homemade products by our professor, Prof. Burnett, with the columns: 1. ProductID 2. Title 3. Price 4. Description 5. ImageLink

The goal was to list all the products currently in the database and their images on the home webpage. A user should be able to click on the image, which brings them to the product webpage /(ProductID)?. From there, the price, name, and description would be available, and they would be able to click on a “Purchase” button, which would return them to the home webpage (indicating they bought the product), and remove the product from the database, so it wouldn’t be available on the home webpage anymore.

The Flask app I programmed is below, and the HTML for the layout, home page, and product page template are below.

Flask App

from flask import Flask, render_template, request
import sqlite3
import pandas as pd 

def query_zip_products(what="all"):
    '''
    Queries `items` table in `products.db` and returns a DataFrame containing columns:
    - ProductID, Title, Price, Description, ImageLink
    Converts from DataFrame into an list of tuples, each one containing item data
    Can specify query for a single product using "what", filters on ProductID
    '''
    # Querying database
    if what == "all":
        cmd = "SELECT ProductID, Title, Price, Description, ImageLink FROM items"
    else:
        cmd = f"SELECT ProductID, Title, Price, Description, ImageLink FROM items WHERE ProductID = {what}"
    with sqlite3.connect("products.db") as conn: 
        df = pd.read_sql_query(cmd, conn)

    # Zipping data
    items = list(zip(df['ProductID'], df['Title'], df['Price'], df['Description'], df['ImageLink']))
    if what != "all":
        items = items[0]
    return items

def remove_product(ProductID):
    '''
    Gets called when a purchase is made, and removes the relevant product 
    (given ProductID) from the current inventory database, `products.db`.
    '''
    cmd = f"DELETE FROM items WHERE ProductID = {ProductID}"
    with sqlite3.connect("products.db") as conn: 
        cursor = conn.cursor()
        cursor.execute(cmd)

app = Flask(__name__)

@app.route('/', methods=["POST","GET"])
def home(): 
    # Handling if coming from a purchase
    purchase_ID = request.form.get("bought-ID", False)
    if purchase_ID:
        remove_product(purchase_ID)
        purchase_name = request.form.get("bought-name")
        purchase_price = request.form.get("bought-price")
        notif = f"You just purchased {purchase_name} for ${purchase_price}. Thanks for your patronage!"
        return render_template("home.html", items=query_zip_products(), notif=notif)
    # Normal handling   
    return render_template("home.html", items=query_zip_products())

@app.route('/<int:ProductID>')
def order(ProductID):
    return render_template("product.html", item=query_zip_products(what=ProductID))

if __name__ == '__main__':
    app.run(debug=True)

layout.html

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="UTF-8">
  <title>Knitted Items</title>
  <link rel="stylesheet" href="static/style.css">
</head>

<body>
  <header>
    <h1>Sarah's Knits and Things</h1>
  </header>
      <main>
      {% block body %}{% endblock %}
      </main>
  <footer>
    <hr>
    <small>&copy; Sarah Burnett, 2025</small>
  </footer>
</body>
</html>

home.html

{% extends "layout.html" %}

{% block body %}

    {% if notif %}
    <h2>{{ notif }}</h2>
    {% endif %}

    <div class="container">
      {% for item in items %}
      <form class="item" method="GET" action="/{{ item[0] }}">
        <button type="submit">
          <img src="{{ item[4] }}" alt="{{ item[1] }}">
          <span class="title"> <br/> {{ item[1] }} </span>
        </button>
      </form>
      {% endfor %}
    </div>

    {% if not items %}
        <h3>Looks like we're out of stock... come back later!</h3>
    {% endif %}
    
{% endblock %}

product.html

{% extends "layout.html" %}

{% block body %}
    <div class="product-container">
      <img src="{{ item[4] }}" alt="{{ item[1] }}">
      <div class="product-details">
        <h2>{{ item[1] }}</h2>
        <span class="price">${{ item[2] }}</span>
        <span class="description"><br/><br/>{{ item[3] }}</span>

        <form method="POST" action="/">
          <button type="submit">
            <span class="title"><br/>Purchase</span>
          </button>
          <input name="bought-ID" type="hidden" value="{{ item[0] }}">
          <input name="bought-name" type="hidden" value="{{ item[1] }}">
          <input name="bought-price" type="hidden" value="{{ item[2] }}">
        </form>

        <a href="/" class="back-button">Back to Home</a>
      </div>
    </div>
{% endblock %}